﻿Imports System.Data.SqlClient
Imports Abyx.SVCMS.Library
Imports Abyx.SVCMS.Library.DB

Public Class SvcManager
    Inherits ManagerBase

#Region "Constructors"
    Public Sub New(ByVal LMuser As UserType)
        MyBase.New(LMuser)
    End Sub
#End Region

#Region "Customers"
    Public Function Get_Customers_Search_COL(SearchText As String) As CustomerTypeCollection

        SearchText = SearchText.Replace(" ", "%'  AND [SearchField] LIKE '%")
        SearchText = " [SearchField] LIKE '%" & SearchText & "%' "
        Dim customers As New CustomerTypeCollection
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim cmd As New SqlCommand("SELECT * " & _
                                      "FROM [COMP_SVC_Customers] " & _
                                      "WHERE " & SearchText & " " & _
                                      "ORDER BY [CustomerNo],[Name]", con)
            Dim dt As DataTable = Utility.GetList(cmd)
            If dt.Rows.Count > 0 Then
                For i As Integer = 0 To dt.Rows.Count - 1
                    Dim row As DataRow = dt.Rows(i)
                    Dim customer As New CustomerType
                    If customer.Load(row) Then customers.Add(customer)
                Next
            End If
            con.Close()
        End Using
        Return customers

    End Function
    Public Function Update_Customer(CustomerTypeObject As CustomerType) As Boolean

        'Do validation
        If CustomerTypeObject.Name = "" Then
            Throw New DataValidationException("Data validation failed, customer name cannot be empty")
        End If
        If CustomerTypeObject.CustomerNo = "" Then
            Throw New DataValidationException("Data validation failed, customer number cannot be empty")
        End If

        'check if exists do insert else do update
        Dim cmd As New SqlCommand("SELECT COUNT(*) " & _
                                  "FROM [SVC_Customers] " & _
                                  "WHERE [CustomerId]='" & CustomerTypeObject.CustomerId.ToString & "'")
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim trans As SqlTransaction = con.BeginTransaction

            cmd.Connection = con
            cmd.Transaction = trans
            If Utility.GetRecordCount(cmd) > 0 Then
                'do update
                With CustomerTypeObject
                    .Connection = con
                    .Transaction = trans
                    .LMUserId = Me.LMuser.UserId
                    Me.LMDate = DateTime.Now
                    .LMDate = Me.LMDate
                    .Update()
                End With

            Else
                'update number sequence
                Dim customerNumber As String = SysManager.Get_SequenceNo(con, trans, SequenceNumeringNamesEnum.CustomerNumberSequence)
                If customerNumber = CustomerTypeObject.CustomerNo Then
                    CustomerTypeObject.CustomerNo = SysManager.Get_SequenceNo(con, trans, SequenceNumeringNamesEnum.CustomerNumberSequence, True)
                End If

                'do insert
                With CustomerTypeObject
                    .Connection = con
                    .Transaction = trans
                    .LMUserId = Me.LMuser.UserId
                    Me.LMDate = DateTime.Now
                    .LMDate = Me.LMDate
                    .Insert()
                End With
            End If

            trans.Commit()
            con.Close()
        End Using

        Return True
    End Function
    Public Function Delete_Customer(CustomerTypeObject As CustomerType) As Boolean

        'Do validation
        If CustomerTypeObject.CustomerId = Guid.Empty Then
            Throw New DataValidationException("Data validation failed, customer cannot be empty")
        End If

        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim trans As SqlTransaction = con.BeginTransaction

            'do delete
            With CustomerTypeObject
                .Connection = con
                .Transaction = trans
                .LMUserId = Me.LMuser.UserId
                Me.LMDate = DateTime.Now
                .LMDate = Me.LMDate
                .Delete()
            End With

            trans.Commit()
            con.Close()
        End Using

        Return True
    End Function
#End Region

#Region ""
    Public Function Get_SODetails_DT(SearchText As String, Optional PendingOnly As Boolean = True) As DataTable

        SearchText = SearchText.Replace(" ", "%'  AND [SearchField] LIKE '%")
        SearchText = " [SearchField] LIKE '%" & SearchText & "%' "
        Dim dt As DataTable = Nothing
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim cmd As New SqlCommand
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            If PendingOnly Then
                cmd.CommandText = "SELECT * " & _
                                  "FROM [COMP_SODetails] " & _
                                  "WHERE (" & SearchText & ") AND ([Status] IN ('Pending')) " & _
                                  "ORDER BY [OrderReceivedDate] DESC"
            Else
                cmd.CommandText = "SELECT * " & _
                                  "FROM [COMP_SODetails] " & _
                                  "WHERE " & SearchText & " " & _
                                  "ORDER BY [OrderReceivedDate] DESC"
            End If
            dt = Utility.GetList(cmd)
            con.Close()
        End Using
        Return dt

    End Function
    Public Function Get_SO_FromSOId(SOId As Guid) As ServiceOrderType

        Dim res As ServiceOrderType = Nothing
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim cmd As New SqlCommand
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            cmd.CommandText = "SELECT * " & _
                                  "FROM [SVC_ServiceOrders] " & _
                                  "WHERE [ServiceOrderId]='" & SOId.ToString & "' "
            Dim dt As DataTable = Utility.GetList(cmd)
            If dt.Rows.Count > 0 Then
                res = New ServiceOrderType
                res.Load(dt.Rows(0))
            End If
            con.Close()
        End Using
        Return res

    End Function
#End Region

End Class
